const { Pool } = require("pg");
const fs = require("fs");
const path = require("path");

/**
 * 导出数据库结构
 */
async function exportDatabaseStructure() {
  // 数据库连接配置
  const pool = new Pool({
    user: "postgres",
    password: "qwe!@#",
    host: "localhost",
    port: 15432,
    database: "Eng",
  });

  try {
    const client = await pool.connect();
    
    try {
      console.log("正在连接到数据库...");
      
      // 创建输出目录
      const outputDir = path.join(__dirname, "../output/db_structure");
      if (!fs.existsSync(outputDir)) {
        fs.mkdirSync(outputDir, { recursive: true });
      }
      
      // 获取表列表，重点关注ENWORDS表
      console.log("正在获取表信息...");
      const tablesResult = await client.query(`
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'
        AND tablename = 'enwords'
      `);
      
      const tables = tablesResult.rows;
      
      if (tables.length === 0) {
        console.log("未找到ENWORDS表");
        return;
      }
      
      console.log(`找到 ${tables.length} 个相关表，正在导出结构...`);
      
      // 为每个表导出结构
      for (const table of tables) {
        const tableName = table.tablename;
        console.log(`正在导出表 ${tableName} 的结构...`);
        
        // 获取表结构
        const structureResult = await client.query(`
          SELECT 
            column_name,
            data_type,
            is_nullable,
            column_default,
            character_maximum_length
          FROM information_schema.columns
          WHERE table_name = $1
          ORDER BY ordinal_position
        `, [tableName]);
        
        // 获取主键信息
        const primaryKeyResult = await client.query(`
          SELECT a.attname AS column_name
          FROM pg_index i
          JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
          WHERE i.indrelid = $1::regclass AND i.indisprimary
        `, [tableName]);
        
        const primaryKey = primaryKeyResult.rows.length > 0 ? primaryKeyResult.rows[0].column_name : null;
        
        // 生成结构信息
        let structureInfo = `Table: ${tableName}\n`;
        structureInfo += `Primary Key: ${primaryKey || 'None'}\n`;
        structureInfo += "Columns:\n";
        
        structureResult.rows.forEach(column => {
          structureInfo += `  ${column.column_name} (${column.data_type}`;
          if (column.character_maximum_length) {
            structureInfo += `(${column.character_maximum_length})`;
          }
          structureInfo += `, ${column.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`;
          if (column.column_default) {
            structureInfo += `, default: ${column.column_default}`;
          }
          structureInfo += ")\n";
        });
        
        // 保存结构信息到文件
        const structureFile = path.join(outputDir, `${tableName}_structure.txt`);
        fs.writeFileSync(structureFile, structureInfo);
        console.log(`表 ${tableName} 的结构已保存到: ${structureFile}`);
        
        // 导出表的部分数据作为示例 (前10行)
        console.log(`正在导出表 ${tableName} 的示例数据...`);
        const sampleDataResult = await client.query(`SELECT * FROM ${tableName} LIMIT 10`);
        
        let sampleDataInfo = `Sample data from table: ${tableName}\n`;
        sampleDataInfo += `Total sample rows: ${sampleDataResult.rows.length}\n\n`;
        
        if (sampleDataResult.rows.length > 0) {
          // 表头
          const columns = Object.keys(sampleDataResult.rows[0]);
          sampleDataInfo += columns.join('\t') + '\n';
          sampleDataInfo += columns.map(() => '----').join('\t') + '\n';
          
          // 数据行
          sampleDataResult.rows.forEach(row => {
            sampleDataInfo += columns.map(col => row[col] || 'NULL').join('\t') + '\n';
          });
        }
        
        const sampleDataFile = path.join(outputDir, `${tableName}_sample_data.txt`);
        fs.writeFileSync(sampleDataFile, sampleDataInfo);
        console.log(`表 ${tableName} 的示例数据已保存到: ${sampleDataFile}`);
      }
      
      console.log("数据库结构导出完成!");
      
    } finally {
      client.release();
    }
  } catch (error) {
    console.error("导出数据库结构时出错:", error);
  } finally {
    await pool.end();
  }
}

// 执行导出
exportDatabaseStructure();